﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using DoNetDrive.Common.Extensions;

namespace DoNetTool.Common
{
    /// <summary>
    /// 数据库访问工具类
    /// </summary>
    public class SqlTool
    {
        
        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        /// <param name="strCmd">命令</param>
        /// <param name="dictParm">参数集合</param>
        /// <returns></returns>
        public static int ExcuteNonQuery(string connStr, string strCmd, Dictionary<string, string> dictParm)
        {
            int effect = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand();
                if (dictParm != null && dictParm.Count > 0)
                {
                    SqlParameter[] cmdParms = new SqlParameter[dictParm.Count];
                    int index = 0;
                    foreach (KeyValuePair<string, string> item in dictParm)
                    {
                        cmdParms[index] = new SqlParameter(item.Key, item.Value);
                        index++;
                    }
                    PrepareCommand(cmd, conn, null, strCmd, cmdParms);
                }
                else
                {
                    PrepareCommand(cmd, conn, null, strCmd);
                }
                try
                {
                    if (strCmd.Contains("select SCOPE_IDENTITY()"))
                    {
                        effect = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                    else
                    {
                        effect = cmd.ExecuteNonQuery();
                    }
                    if (cmd.Parameters.Count > 0)
                    {
                        cmd.Parameters.Clear();
                    }
                }
                catch (Exception ex)
                {

                }

            }

            return effect;
        }
        /// <summary>
        /// 为SqlCommand添加 连接对象，事务对象，参数数组
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">连接对象</param>
        /// <param name="trans">事务对象</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParms">参数数组</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms = null)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    SqlParameter pp = (SqlParameter)((ICloneable)parameter).Clone();
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(pp);
                }
            }
        }

        /// <summary>
        /// 查询并转换成对象集合
        /// </summary>
        /// <typeparam name="T2">对象类型</typeparam>
        /// <param name="connStr">数据库连接串</param>
        /// <param name="strSelectCmd">查询语句</param>
        /// <param name="dictParm">参数集合</param>
        /// <returns></returns>
        public static List<T2> ExcuteList<T2>(string connStr, string strSelectCmd, Dictionary<string, object> dictParm)
        {
            //1.创建连接通道
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand();
                if (dictParm != null && dictParm.Count > 0)
                {
                    SqlParameter[] cmdParms = new SqlParameter[dictParm.Count];
                    int index = 0;
                    foreach (KeyValuePair<string, object> item in dictParm)
                    {
                        cmdParms[index] = new SqlParameter(item.Key, item.Value);
                        index++;
                    }
                    PrepareCommand(cmd, conn, null, strSelectCmd, cmdParms);
                }
                else
                {
                    PrepareCommand(cmd, conn, null, strSelectCmd);
                }
                //2.创建适配器
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                //2.1设置查询命令的参数

                //3.数据表
                DataTable dt = new DataTable();
                List<T2> list = new List<T2>();
                try
                {
                    //4.将数据查询并填充到数据表中
                    da.Fill(dt);
                    //5.将DataTable转成泛型集合List<T2>

                    if (dt.Rows.Count > 0)
                    {
                        //7.遍历数据行，将行数据存入 实体对象中，并添加到 泛型集合中list
                        foreach (DataRow row in dt.Rows)
                        {//7.1先获得泛型的类型(里面包含该类的所有信息 属性 方法 字段...........)
                            Type t = typeof(T2);
                            //7.2根据类型创建该类型的对象
                            T2 model = (T2)Activator.CreateInstance(t);// new MODEL.Classes()
                                                                       //7.3根据类型 获得 该类型的 所有属性定义
                            PropertyInfo[] properties = t.GetProperties();
                            //7.4遍历属性数组
                            foreach (PropertyInfo p in properties)
                            {
                                if (!dt.Columns.Contains(p.Name))
                                {
                                    continue;
                                }
                                //7.4.1获得属性名，作为列名
                                string colName = p.Name;
                                //7.4.2根据列名 获得当前循环行对应列的值
                                object colValue = row[colName];
                                if (colValue.ToString() == "")
                                {
                                    continue;
                                }

                                //7.4.3将 列值 赋给 model对象的p属性
                                //model.ID=colValue;
                                p.SetValue(model, colValue, null);
                            }
                            //7.5将装好 了行数据的 实体对象 添加到 泛型集合中
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                }

                return list;
            }

        }


        /// <summary>
        /// 组装分页查询语句
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="orderby">排序列</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="pageIndex">当前页数</param>
        /// <returns></returns>
        public static string GetPagerSQL(string sql, string orderby, int? pageSize, int? pageIndex)
        {

            string strSQL = sql;// + (string.IsNullOrEmpty(where) ? "" : where);

            //(string.IsNullOrEmpty(orderby) ? "" : @" ORDER BY " + orderby);
            if (pageSize == null || pageIndex == null)
            {
                return strSQL;
            }
            var start = (int)(pageIndex - 1) * (int)pageSize + 1;
            var end = (int)(pageIndex) * (int)pageSize;
            return
                @"
                SELECT * FROM
                (
                    SELECT t.*, row_number() over(order by " +
                orderby + @") AS ROWINDEX FROM 
                    (
                        " + strSQL +
                @"
                    ) t
                ) t2 
                WHERE t2.ROWINDEX BETWEEN " + start + " AND " + end;
        }

        /// <summary>
        /// 组装查询总记录数语句
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public static string GetQueryTotalRowCount(string sql)
        {
            return "SELECT count(*) FROM (" + sql + ")t";
        }


        /// <summary>
        /// 组装Insert语句
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="InsertCols">Insert列名</param>
        /// <param name="InsertParam">Insert参数</param>
        /// <returns></returns>
        public static string CreateInsertSql(string TableName, string InsertCols, string InsertParam = null)
        {
            //string sql = string.Concat("insert into ", TableName,"(Name,IsDel,Creator,CreateTime) values(@name,0,'',getdate())");
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into " + TableName + "(");
            string[] cols = InsertCols.Split(',');

            for (int i = 0; i < cols.Length; i++)
            {
                sb.Append(cols[i]);
                if (i != cols.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(") values(");
            if (!string.IsNullOrWhiteSpace(InsertParam))
            {
                string[] param = InsertParam.Split(',');
                for (int i = 0; i < param.Length; i++)
                {
                    sb.Append(param[i]);
                    if (i != param.Length - 1)
                    {
                        sb.Append(",");
                    }
                }
            }
            else
            {
                for (int i = 0; i < cols.Length; i++)
                {
                    sb.Append("@" + cols[i]);
                    if (i != cols.Length - 1)
                    {
                        sb.Append(",");
                    }
                }
            }
            sb.Append(")");
            string sql = sb.ToString();
            return sql;
        }

        /// <summary>
        /// 组装Update语句
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="UpdateCols">Update列名</param>
        /// <param name="UpdateParam">Update参数</param>
        /// <param name="WhereSql">条件语句</param>
        /// <returns></returns>
        public static string CreateUpdateSql(string TableName, string UpdateCols, string UpdateParam, string WhereSql)
        {
            StringBuilder sb = new StringBuilder();
            string[] cols = UpdateCols.Split(',');
            string[] param = UpdateParam.Split(',');
            sb.Append("update " + TableName + " set ");
            for (int i = 0; i < cols.Length; i++)
            {
                sb.Append(cols[i] + "=" + param[i]);
                if (i != cols.Length - 1)
                {
                    sb.Append(",");
                }
            }
            if (!string.IsNullOrEmpty(WhereSql))
            {
                sb.Append(" where " + WhereSql);
            }


            string sql = sb.ToString();
            return sql;
        }

        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="strSelectCmd"></param>
        /// <param name="dictParm"></param>
        /// <returns></returns>
        public static object GetSingleVal(string connStr, string strSelectCmd, Dictionary<string, object> dictParm)
        {
            object obj = null;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    if (dictParm != null && dictParm.Count > 0)
                    {
                        SqlParameter[] cmdParms = new SqlParameter[dictParm.Count];
                        int index = 0;
                        foreach (KeyValuePair<string, object> item in dictParm)
                        {
                            cmdParms[index] = new SqlParameter(item.Key, item.Value);
                            index++;
                        }
                        PrepareCommand(cmd, conn, null, strSelectCmd, cmdParms);
                    }
                    else
                    {
                        PrepareCommand(cmd, conn, null, strSelectCmd, null);
                    }
                    obj = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {

                }
            }

            return obj;
        }

        /// <summary>
        /// 执行多条sql语句
        /// </summary>
        /// <param name="list">sql语句集合</param>
        /// <param name="result"></param>
        /// <param name="resultMessage"></param>
        public static bool ExecuteText(List<string> list, string connStr, out string resultMessage)
        {
            using (SqlConnection con = new SqlConnection(connStr))
            {
                con.Open();

                SqlTransaction tran = con.BeginTransaction();

                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.Transaction = tran;

                try
                {
                    foreach (string item in list)
                    {
                        if (string.IsNullOrWhiteSpace(item))
                        {
                            continue;
                        }
                        com.CommandText = item;
                        com.ExecuteNonQuery();
                    }

                    tran.Commit();

                    resultMessage = "执行成功";
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    resultMessage = ex.Message;
                    return false;
                }
            }
        }

        public class FieldDef
        {
            public string Name;
            public string Type;
            public int Len;
            public string SQLName;

            public void SetValue(string sFieldDetail)
            {
                var stmp = sFieldDetail.Split(',');
                Name = stmp[0].Trim();//.ToLower()
                Type = stmp[1].Trim();
                Len = System.Convert.ToInt32(stmp[2].Trim());
                if (stmp.Length == 4)
                    SQLName = stmp[3].Trim();
                else
                    SQLName = Name;
            }

            public static List<FieldDef> Split(string sFieldDetails)
            {
                var sTmp = sFieldDetails.Split(new[] { StringUtil.StringNewLine }, StringSplitOptions.RemoveEmptyEntries);
                var oList = new List<FieldDef>(sTmp.Length);
                foreach (var detail in sTmp)
                {
                    var f = new FieldDef();
                    f.SetValue(detail);
                    oList.Add(f);
                }
                return oList;
            }
        }


        public static string GetValueOnForm(NameValueCollection fmd, string sKey)
        {
            var sValues = fmd.GetValues(sKey);
            if (sValues == null)
                return string.Empty;
            else
                return sValues[0];
        }

        /// <summary>
        /// 创建SQL 查询语句
        /// </summary>
        /// <param name="sWhere"></param>
        /// <param name="sFields">设定查询字段名和类型   
        /// 格式每组回车分隔，组中,号分隔，组中元素0表示字段名，元素2表示字段类型（str---字符串,int--数值,date--时间），元素3表示长度。</param>
        /// <param name="form"></param>
        /// <returns></returns>
        public static string CreateSQLWhere(string sWhere, string sFields, NameValueCollection form)
        {
            var sSQL = new System.Text.StringBuilder(1024);
            sSQL.Append(sWhere);
            var oFields = FieldDef.Split(sFields);
            var sFormKeys = form.AllKeys;
            var sOperList = "=,<>,>,<,>=,<=,!=,!<,!>".Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            foreach (var oField in oFields)
            {
                if (oField.Type != "null")
                {
                    if (oField.Type == "date")
                    {
                        string sBeginKey, sEndKey;
                        string sBeginValue, sEndValue;
                        sBeginKey = $"{oField.Name}_Begin";
                        sEndKey = $"{oField.Name}_End";

                        sBeginValue = GetValueOnForm(form, sBeginKey);
                        sEndValue = GetValueOnForm(form, sEndKey);
                        if ((!string.IsNullOrEmpty(sBeginValue)) & (!string.IsNullOrEmpty(sEndValue)))
                        {
                            sBeginValue = StringUtil.CheckInputText(sBeginValue, oField.Len);
                            sEndValue = StringUtil.CheckInputText(sEndValue, oField.Len);

                            if (sBeginValue.IsDate() & sEndValue.IsDate())
                            {
                                if (sSQL.Length > 0)
                                    sSQL.Append(" And ");
                                sSQL.Append($"{oField.SQLName} between '{DateTime.Parse(sBeginValue).ToDateTimeStr()}' and '{DateTime.Parse(sEndValue).ToDateTimeStr()}'");
                            }
                        }
                    }
                    else
                    {
                        var iKeyIndex = sFormKeys.FindTextIndex(oField.Name);
                        if (iKeyIndex > -1)
                        {
                            var sOpt = GetValueOnForm(form, $"{oField.Name}_oper");
                            if (string.IsNullOrEmpty(sOpt))
                                sOpt = "=";

                            string oValue;


                            if (sSQL.Length > 0)
                                sSQL.Append(" And ");


                            oValue = form.GetValues(iKeyIndex)[0];
                            if (!("str,pwdList".IndexOf(oField.Type) >= 0))
                                oValue = StringUtil.CheckInputText(oValue, oField.Len);


                            switch (oField.Type)
                            {
                                case "str":
                                    {
                                        if (sOperList.FindTextIndex(sOpt) > -1)
                                        {
                                            oValue = StringUtil.CheckInputText(oValue, oField.Len);
                                            sSQL.Append($"{oField.SQLName} {sOpt} '{oValue}'");
                                        }

                                        if (sOpt.IsSame("Like"))
                                        {
                                            oValue = StringUtil.CheckInputText(oValue, oField.Len);
                                            sSQL.Append($"{oField.SQLName} Like '%{oValue}%'");
                                        }

                                        if (sOpt.IsSame("List"))
                                        {
                                            oValue = StringUtil.CheckStringList(oValue, oField.Len, false);
                                            sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                        }

                                        break;
                                    }

                                case "pwd":
                                    {
                                        if (oValue.IsNum())
                                        {
                                            if (sOperList.FindTextIndex(sOpt) > -1)
                                                sSQL.Append($"{oField.SQLName} {sOpt} '{oValue}'");

                                            if (sOpt.IsSame("Like"))
                                                sSQL.Append($"{oField.SQLName} Like '%{oValue}%'");
                                        }

                                        break;
                                    }

                                case "pwdList":
                                    {
                                        oValue = StringUtil.CheckStringList(oValue, oField.Len, false);
                                        if (!string.IsNullOrEmpty(oValue))
                                        {
                                            if (oField.Name.EndsWith("s") & oField.Name == oField.SQLName)
                                            {
                                                oField.Name = oField.Name.Substring(0, oField.Name.Length - 1);
                                                oField.SQLName = oField.Name;
                                            }


                                            if (sOpt == "=")
                                                sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                            else if (sOpt == "!=")
                                                sSQL.Append($"{oField.SQLName} not in ( {oValue} )");
                                        }

                                        break;
                                    }

                                case "int":
                                    {
                                        if (oValue.IsNum())
                                        {
                                            if (sOpt.IsSame("Range"))
                                            {
                                                string sEndKey;
                                                string sEndValue;
                                                sEndKey = $"{oField.Name}_End";

                                                sEndValue = GetValueOnForm(form, sEndKey);
                                                sEndValue = StringUtil.CheckInputText(sEndValue, oField.Len);

                                                if (sEndValue.IsNum())
                                                    sSQL.Append($"{oField.SQLName} between {oValue} And {sEndValue}");
                                            }
                                            else if (sOperList.FindTextIndex(sOpt) > -1)
                                                sSQL.Append($"{oField.SQLName} {sOpt} {oValue}");
                                        }

                                        break;
                                    }

                                case "IDList":
                                    {
                                        oValue = StringUtil.CheckIDListNoDelete(oValue);
                                        if (oField.Name.EndsWith("s") & oField.Name == oField.SQLName)
                                        {
                                            oField.Name = oField.Name.Substring(0, oField.Name.Length - 1);
                                            oField.SQLName = oField.Name;
                                        }


                                        if (sOpt == "=")
                                            sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                        else if (sOpt == "!=")
                                            sSQL.Append($"{oField.SQLName} not in ( {oValue} )");
                                        break;
                                    }
                                case "StrList":
                                    {
                                        oValue = StringUtil.CheckStrListNoDelete(oValue);
                                        if (oField.Name.EndsWith("s") & oField.Name == oField.SQLName)
                                        {
                                            oField.Name = oField.Name.Substring(0, oField.Name.Length - 1);
                                            oField.SQLName = oField.Name;
                                        }


                                        if (sOpt == "=")
                                            sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                        else if (sOpt == "!=")
                                            sSQL.Append($"{oField.SQLName} not in ( {oValue} )");
                                        break;
                                    }
                            }
                        }
                    }
                }
            }

            return sSQL.ToString();
        }


    }
}
